package com.basic.im.utils;

import com.google.common.collect.Maps;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * @author: hsg
 * @date: 2018年7月26日 下午8:57:05
 * @version: 1.0
 * @Description:    Excel 表格工具类
 */
public class ExcelUtil{


	private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class.getName());


	/**
	 *写Excel文件
	 * @param response  : 响应对象
	 * @param fileName  : 下载文件名称
	 * @param title     : 标题
	 * @param dataList  : 内容
	 * @return
	 * @throws Exception
	 */
	public static boolean writeExcel(HttpServletResponse response,String fileName,String[] title,List<String[]> dataList) throws Exception {
		boolean flag = false;

		try {
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFCellStyle hssfTitleCellStyle = getHSSFTitleStyle(wb);

			int pageSize = 65535;
			int listSize = dataList.size();
			int sheetSize = 0;
			if(listSize % pageSize == 0){
				sheetSize = listSize/pageSize;
			}else{
				sheetSize = (listSize/pageSize)+1;
			}

			if(sheetSize == 0){
				HSSFSheet sheet = wb.createSheet();
				HSSFRow row = sheet.createRow(0);
				row.setHeight((short) 600);//目的是想把行高设置成25px
				row.setRowStyle(hssfTitleCellStyle);
				sheet.setDefaultColumnWidth(20);
				sheet.setDefaultRowHeightInPoints(20);
				//title
				for (int i = 0;i < title.length;i++) {
					HSSFCell cell = row.createCell(i);
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell.setCellStyle(hssfTitleCellStyle);
					cell.setCellValue(title[i]);
				}
			}else{
				int start = 0;
				int end = 0;
				for(int s=0;s<sheetSize;s++){
					start = s * pageSize;
					if(s == (sheetSize-1)) {
						end = listSize;
					} else {
						end = (s+1) * pageSize;
					}
					HSSFSheet sheet = wb.createSheet();
					HSSFRow row = sheet.createRow(0);
					row.setHeight((short) 600);//目的是想把行高设置成25px
					row.setRowStyle(hssfTitleCellStyle);
					sheet.setDefaultColumnWidth(20);
					sheet.setDefaultRowHeightInPoints(20);
					//title
					for (int i = 0;i < title.length;i++) {
						HSSFCell cell = row.createCell(i);
						cell.setCellType(HSSFCell.CELL_TYPE_STRING);
						cell.setCellStyle(hssfTitleCellStyle);
						cell.setCellValue(title[i]);
					}
					//context
					HSSFCellStyle hssfCellStyle = getHSSFCellStyle(wb);
					int row_index = 1;
					for(int j = start;j < end;j++){
						HSSFRow data_row = sheet.createRow(row_index);
						row_index ++;
						String[] cellList = dataList.get(j);
						int len = cellList.length;
						for (int k=0;k<len;k++) {
							HSSFCell cell = data_row.createCell(k);
							String value = cellList[k];
							cell.setCellType(HSSFCell.CELL_TYPE_STRING);
							cell.setCellStyle(hssfCellStyle);
							cell.setCellValue(value);
						}
					}
				}
			}
			response.reset();
			response.setHeader("Content-Disposition", "attachment;filename="+ new String(fileName.getBytes(), "iso8859-1"));
			ServletOutputStream out = response.getOutputStream();
			wb.write(out);
			// 弹出下载对话框
			out.close();
			flag = true;
		} catch (Exception e) {
			throw e;
		}
		return flag;
	}


	private static HSSFCellStyle getHSSFCellStyle(HSSFWorkbook wb) {
		// TODO Auto-generated method stub
		return null;
	}


	/**
	 */
	private static HSSFCellStyle getHSSFTitleStyle(HSSFWorkbook wb) {
		// 标题样式
		HSSFCellStyle titleStyle = wb.createCellStyle();
		titleStyle.setAlignment(CellStyle.ALIGN_CENTER); // 水平对齐
		titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //垂直对齐
		titleStyle.setLocked(true); // 样式锁定
		titleStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
		Font titleFont = wb.createFont();
		titleFont.setFontHeightInPoints((short) 16);

		titleFont.setFontName("微软雅黑");
		titleStyle.setFont(titleFont);

		return titleStyle;
	}





	/**
	 * 用于测试
	 * @param args
	 */

	/*public static void main(String[] args) {
	        String path = "E://demo.xlsx";
	        String name = "test";
	        List<String> titles =Lists.newArrayList();
	        titles.add("id");
	        titles.add("name");
	        titles.add("age");
	        titles.add("birthday");
	        titles.add("gender");
	        titles.add("date");
	        List<Map<String, Object>> values = Lists.newArrayList();
	        for (int i = 0; i < 10; i++) {
	            Map<String, Object> map = Maps.newHashMap();
	            map.put("id", i + 1D);
	            map.put("name", "test_" + i);
	            map.put("age", i * 1.5);
	            map.put("gender", "man");
	            map.put("birthday", new Date());
	            map.put("date",  Calendar.getInstance());
	            values.add(map);
	        }
	        System.out.println(writerExcel(path, name, titles, values));
	    }*/


	/**
	 * 数据写入Excel文件
	 *
	 * @param path 文件路径，包含文件全名，例如：D://file//demo.xls
	 * @param name sheet名称
	 * @param titles 行标题列
	 * @param values 数据集合，key为标题，value为数据
	 * @return True\False
	 */

	    /*public static boolean writerExcel(String path, String name, List<String> titles, List<Map<String, Object>> values) {
	        LOGGER.info("path : {}", path);
	        String style = path.substring(path.lastIndexOf("."), path.length()).toUpperCase(); // 从文件路径中获取文件的类型
	        return generateWorkbook(path, name, style, titles, values);
	    }*/

	/**
	 * 将数据写入指定path下的Excel文件中
	 *
	 * @param name   sheet名
	 * @param style  Excel类型   xls、 xlsx
	 * @param titles 标题串
	 * @param values 内容集
	 * @return  Workbook
	 */
	public static Workbook generateWorkbook(String name, String style, List<String> titles, List<Map<String, Object>> values) {

		LOGGER.info("file style : {}", style);

		Workbook workbook;
		if ("XLS".equals(style.toUpperCase())) {
			workbook = new HSSFWorkbook();
		} else { //xlsx
			workbook = new XSSFWorkbook();
		}
		// 生成一个表格
		Sheet sheet;

		if (null == name || "".equals(name)) {
			sheet = workbook.createSheet(); // name 为空则使用默认值
		} else {
			sheet = workbook.createSheet(name);
		}
		//设置表格默认列宽度为15个字节
		sheet.setDefaultColumnWidth((short) 15);
		// 生成样式
		Map<String, CellStyle> styles = createStyles(workbook);
		/*
		 * 创建标题行
		 */
		Row row = sheet.createRow(0);
		// 存储标题在Excel文件中的序号
		Map<String, Integer> titleOrder = Maps.newHashMap();

		for (int i = 0; i < titles.size(); i++) {
			Cell cell = row.createCell(i);
			cell.setCellStyle(styles.get("header"));
			String title = titles.get(i);
			cell.setCellValue(title);
			titleOrder.put(title, i);
		}

		/*
		 * 写入正文
		 */
		Iterator<Map<String, Object>> iterator = values.iterator();
		int index = 0; // 行号
		while (iterator.hasNext()) {
			index++; // 出去标题行，从第一行开始写
			row = sheet.createRow(index);
			Map<String, Object> value = iterator.next();
			for (Map.Entry<String, Object> map : value.entrySet()) {
				//获取列名
				String title = map.getKey();
				//根据列名获取序号
				int i = titleOrder.get(title);
				//在指定序号处创建cell
				Cell cell = row.createCell(i);
				//设置cell的样式
				// if (0<index&&index % 2 == 1) {
				// 	cell.setCellStyle(styles.get("cellA"));
				// } else {
				// 	cell.setCellStyle(styles.get("cellA"));
				// }
				cell.setCellStyle(styles.get("cellA"));
				//获取列的值
				Object object = map.getValue();
				// 判断object的类型
				SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				if (object instanceof Double) {
					cell.setCellValue((Double) object);
				} else if (object instanceof Date) {
					String time = simpleDateFormat.format((Date) object);
					cell.setCellValue(time);
				} else if (object instanceof Calendar) {
					Calendar calendar = (Calendar) object;
					String time = simpleDateFormat.format(calendar.getTime());
					cell.setCellValue(time);
				} else if (object instanceof Boolean) {
					cell.setCellValue((Boolean) object);
				} else {
					if(null != object) {
						cell.setCellValue(object.toString());
					}
				}
			}
		}

		/*
		 * 写入到文件中
		 */
	        /*boolean isCorrect = false;
	        try {
	            File file = new File(path);
	            OutputStream outputStream = new FileOutputStream(file);
	            workbook.write(outputStream);
	            outputStream.close();
	            isCorrect = true;
	        } catch (IOException e) {
	            isCorrect = false;
	            LOGGER.error("write Excel file error : {}", e.getMessage());
	        }*/

		return workbook;
	}


	/**
	 * 创建表格样式
	 * @param wb
	 * @return
	 */
	private static Map<String, CellStyle> createStyles(Workbook wb) {
		Map<String, CellStyle> styles = Maps.newHashMap();

		// 标题样式
		CellStyle titleStyle = wb.createCellStyle();
		titleStyle.setAlignment(CellStyle.ALIGN_CENTER); // 水平对齐
		titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //垂直对齐
		titleStyle.setLocked(true); // 样式锁定
		titleStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
		Font titleFont = wb.createFont();
		titleFont.setFontHeightInPoints((short) 16);

		titleFont.setFontName("微软雅黑");
		titleStyle.setFont(titleFont);
		styles.put("title", titleStyle);

		// 文件头样式
		CellStyle headerStyle = wb.createCellStyle();
		headerStyle.setAlignment(CellStyle.ALIGN_CENTER);
		headerStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		headerStyle.setFillForegroundColor(IndexedColors.LIGHT_BLUE.getIndex()); // 前景色
		headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); // 颜色填充方式
		headerStyle.setWrapText(true);
		headerStyle.setBorderRight(CellStyle.BORDER_THIN); // 设置边界
		headerStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
		headerStyle.setBorderLeft(CellStyle.BORDER_THIN);
		headerStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		headerStyle.setBorderTop(CellStyle.BORDER_THIN);
		headerStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
		headerStyle.setBorderBottom(CellStyle.BORDER_THIN);
		headerStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		Font headerFont = wb.createFont();
		headerFont.setFontHeightInPoints((short) 12);
		headerFont.setColor(IndexedColors.WHITE.getIndex());
		titleFont.setFontName("微软雅黑");
		headerStyle.setFont(headerFont);
		styles.put("header", headerStyle);

		Font cellStyleFont = wb.createFont();
		cellStyleFont.setFontHeightInPoints((short) 12);
		cellStyleFont.setColor(IndexedColors.BLUE_GREY.getIndex());
		cellStyleFont.setFontName("微软雅黑");

		// 正文样式A
		CellStyle cellStyleA = wb.createCellStyle();
		cellStyleA.setAlignment(CellStyle.ALIGN_CENTER); // 居中设置
		cellStyleA.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		cellStyleA.setWrapText(true);
		cellStyleA.setBorderRight(CellStyle.BORDER_THIN);
		cellStyleA.setRightBorderColor(IndexedColors.BLACK.getIndex());
		cellStyleA.setBorderLeft(CellStyle.BORDER_THIN);
		cellStyleA.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		cellStyleA.setBorderTop(CellStyle.BORDER_THIN);
		cellStyleA.setTopBorderColor(IndexedColors.BLACK.getIndex());
		cellStyleA.setBorderBottom(CellStyle.BORDER_THIN);
		cellStyleA.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		cellStyleA.setFont(cellStyleFont);
		styles.put("cellA", cellStyleA);

		// 正文样式B:添加前景色为浅黄色
		CellStyle cellStyleB = wb.createCellStyle();
		cellStyleB.setAlignment(CellStyle.ALIGN_CENTER);
		cellStyleB.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		cellStyleB.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
		cellStyleB.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cellStyleB.setWrapText(true);
		cellStyleB.setBorderRight(CellStyle.BORDER_THIN);
		cellStyleB.setRightBorderColor(IndexedColors.BLACK.getIndex());
		cellStyleB.setBorderLeft(CellStyle.BORDER_THIN);
		cellStyleB.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		cellStyleB.setBorderTop(CellStyle.BORDER_THIN);
		cellStyleB.setTopBorderColor(IndexedColors.BLACK.getIndex());
		cellStyleB.setBorderBottom(CellStyle.BORDER_THIN);
		cellStyleB.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		cellStyleB.setFont(cellStyleFont);
		styles.put("cellB", cellStyleB);

		return styles;
	}


	private final static String xls = "xls";
	private final static String xlsx = "xlsx";

	/**
	 * 读入excel文件，解析后返回
	 * @param file
	 * @throws IOException
	 */
	public static List<String[]> readExcel(MultipartFile file) throws IOException {
		//检查文件
		checkFile(file);
		//获得Workbook工作薄对象
		Workbook workbook = getWorkBook(file);
		//创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
		List<String[]> list = new ArrayList<String[]>();
		if(workbook != null){
			for(int sheetNum = 0;sheetNum < workbook.getNumberOfSheets();sheetNum++){
				//获得当前sheet工作表
				Sheet sheet = workbook.getSheetAt(sheetNum);
				if(sheet == null){
					continue;
				}
				//获得当前sheet的开始行
				int firstRowNum  = sheet.getFirstRowNum();
				//获得当前sheet的结束行
				int lastRowNum = sheet.getLastRowNum();
				//循环除了第一行的所有行
				for(int rowNum = firstRowNum+1;rowNum <= lastRowNum;rowNum++){
					//获得当前行
					Row row = sheet.getRow(rowNum);
					if(row == null){
						continue;
					}
					//获得当前行的开始列
					int firstCellNum = row.getFirstCellNum();
					//获得当前行的列数
					int lastCellNum = row.getPhysicalNumberOfCells();
					String[] cells = new String[row.getPhysicalNumberOfCells()];
					//循环当前行
					for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
						Cell cell = row.getCell(cellNum);
						cells[cellNum] = getCellValue(cell);
					}
					list.add(cells);
				}
			}
			// 新版本，close方法被移除了
			// workbook.close();
		}
		return list;
	}
	public static void checkFile(MultipartFile file) throws IOException{
		//判断文件是否存在
		if(null == file){
			LOGGER.error("文件不存在！");
			throw new FileNotFoundException("文件不存在！");
		}
		//获得文件名
		String fileName = file.getOriginalFilename();
		//判断文件是否是excel文件
		if(!fileName.endsWith(xls) && !fileName.endsWith(xlsx)){
			LOGGER.error(fileName + "不是excel文件");
			throw new IOException(fileName + "不是excel文件");
		}
	}
	public static Workbook getWorkBook(MultipartFile file) {
		//获得文件名
		String fileName = file.getOriginalFilename();
		//创建Workbook工作薄对象，表示整个excel
		Workbook workbook = null;
		try {
			//获取excel文件的io流
			InputStream is = file.getInputStream();
			//根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
			if(fileName.endsWith(xls)){
				//2003
				workbook = new HSSFWorkbook(is);
			}else if(fileName.endsWith(xlsx)){
				//2007
				workbook = new XSSFWorkbook(is);
			}
		} catch (IOException e) {
			LOGGER.info(e.getMessage());
		}
		return workbook;
	}
	public static String getCellValue(Cell cell){
		String cellValue = "";
		if(cell == null){
			return cellValue;
		}
		//把数字当成String来读，避免出现1读成1.0的情况
		if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
			cell.setCellType(Cell.CELL_TYPE_STRING);
		}
		//判断数据的类型
		switch (cell.getCellType()){
			case Cell.CELL_TYPE_NUMERIC: //数字
				cellValue = String.valueOf(cell.getNumericCellValue());
				break;
			case Cell.CELL_TYPE_STRING: //字符串
				cellValue = String.valueOf(cell.getStringCellValue());
				break;
			case Cell.CELL_TYPE_BOOLEAN: //Boolean
				cellValue = String.valueOf(cell.getBooleanCellValue());
				break;
			case Cell.CELL_TYPE_FORMULA: //公式
				cellValue = String.valueOf(cell.getCellFormula());
				break;
			case Cell.CELL_TYPE_BLANK: //空值
				cellValue = "";
				break;
			case Cell.CELL_TYPE_ERROR: //故障
				cellValue = "非法字符";
				break;
			default:
				cellValue = "未知类型";
				break;
		}
		return cellValue;
	}


}
